<?php
//пароль
if (!defined('INWEB')) return2();
$a=getVar('a');
includeLang('reports');
$from=getVar('date_from','date');
$to=getVar('date_to','date');
$qfrom=date2SQLdate($from);
$qto=date2SQLdate($to);

$emp1=getVar('employee1','int',true);
$emp2=getVar('employee2','int',true);
$brok1=getVar('broker1','int',true);
$brok2=getVar('broker2','int',true);
$taker=getVar('ins_takers','int',true);
$company=getVar('ins_comps','int',true);
$report=getVar('report_type');
$parse=$Lang;
$parse['page']=$page;
$parse['employee_opt1']='';
$parse['employee_opt2']='';
$sql->query("SELECT id, name FROM employees");
while($emp=$sql->fetch_array())
{
    $selected1=($emp['id']==$emp1)?' selected="selected"':'';
    $selected2=($emp['id']==$emp2)?' selected="selected"':'';
    $parse['employee_opt1'].='<option value="'.$emp['id'].'"'.$selected1.'>'.$emp['name'].'</option>';
    $parse['employee_opt2'].='<option value="'.$emp['id'].'"'.$selected2.'>'.$emp['name'].'</option>';
}

$parse['broker_opt1']='';
$parse['broker_opt2']='';
$sql->query("SELECT id, name FROM brokers");
while($brok=$sql->fetch_array())
{
    $selected1=($brok['id']==$brok1)?' selected="selected"':'';
    $selected2=($brok['id']==$brok2)?' selected="selected"':'';
    $parse['broker_opt1'].='<option value="'.$brok['id'].'"'.$selected1.'>'.$brok['name'].'</option>';
    $parse['broker_opt2'].='<option value="'.$brok['id'].'"'.$selected2.'>'.$brok['name'].'</option>';
}

$parse['ins_takers_opt']='';
$sql->query("SELECT id, name FROM clients");
while($client=$sql->fetch_array())
{
    $selected=($client['id']==$taker)?' selected="selected"':'';
    $parse['ins_takers_opt'].='<option value="'.$client['id'].'"'.$selected.'>'.stripslashes(str_replace('&','&amp;',$client['name'])).'</option>'."\n";
}

$parse['ins_comps_opt']='';
$sql->query("SELECT id, abbr FROM insurance_companies");
while($ic=$sql->fetch_array())
{
    $selected=($ic['id']==$company)?' selected="selected"':'';
    $parse['ins_comps_opt'].='<option value="'.$ic['id'].'"'.$selected.'>'.$ic['abbr'].'</option>';
}
switch($report)
{
    case 'ins_contracts':
        $parse['ins_contracts_s']='checked="checked"';
    break;
    case 'payments':
        $parse['payments_s']='checked="checked"';
    break;
    case 'rpayments':
        $parse['rpayments_s']='checked="checked"';
    break;
    case 'pol_restore':
        $parse['pol_restore_s']='checked="checked"';
    break;
    case 'pol_payable':
        $parse['pol_payable_s']='checked="checked"';
    break;
    case 'pol_canceled':
        $parse['pol_canceled_s']='checked="checked"';
    break;
}
$parse['date_from_v']=$from;
$parse['date_to_v']=$to;
$tpl->parsetemplate('period',$parse);
if($_POST || isset($_GET['save']) || isset($_GET['pg']))
{
    try
    {
        if(isset($_GET['save']))
        {
            $content=writeXLSHead()."\n";
            $content2='<Row><Cell><Data ss:Type="String">'.sprintf($Lang['time_period_from_to'],$from,$to).'</Data></Cell></Row>';
            echo $Lang['generating_xml_content'].$_POST['report_type'].'<br />';
        }
        else
        {
            if(isset($_GET['pg']))
                $start = getVar('pg');
            else 
                $start = 1;
            if(!is_numeric($start) || $start == 0)
                $start = 1;
            $start = abs($start) - 1;
            $startlimit = $start * getConfig('settings','items_per_page','100');
            $rows=getConfig('settings','items_per_page','100');
            $npage=$page;
            $npage.='&report_type='.$report;
            $npage.='&date_from='.$from;
            $npage.='&date_to='.$to;
            $npage.='&ins_comps='.$company;
            $npage.='&ins_takers='.$taker;
            $npage.='&emp1='.$emp1;
            $npage.='&emp2='.$emp2;
            $npage.='&broker1='.$brok1;
            $npage.='&broker2='.$brok2;
            $lim="LIMIT $startlimit, $rows";                
        }
        if($emp1!=null && $emp1!='')
        {
            if($emp2!=null && $emp2!='')
            {
                if($emp2!=$emp1)
                {
                    if($emp1>$emp2)
                    {
                        $temp=$emp1;
                        $emp1=$emp2;
                        $emp2=$temp;
                    }
                    $qry_emp_add=" AND employees.id>='$emp1' AND employees.id<='$emp2'";
                }
                else
                {
                    $qry_emp_add=" AND employees.id='$emp1'";
                }
            }
            else
            {
                $qry_emp_add=" AND employees.id='$emp1'";
            }
            if(isset($_GET['save']))
            {
                $qemp1=$sql->query("SELECT `abbr`, `name` FROM `employees` WHERE id='$emp1';");
                $semp1=$sql->fetch_array($qemp1);
                $content2.='<Row><Cell><Data ss:Type="String">'.$Lang['employee'].': '.$semp1['abbr'].' ('.$semp1['name'].') '.$Lang['to'];
                if($emp2!=$emp1)
                {
                    $qemp2=$sql->query("SELECT `abbr`, `name` FROM `employees` WHERE id='$emp2';");
                    $semp2=$sql->fetch_array($qemp2);
                    $content2.=' '.$semp2['abbr'].' ('.$semp2['name'].') ';
                }
                else
                {
                    $content2.=' '.$semp1['abbr'].' ('.$semp1['name'].') ';
                }
                $content2.='</Data></Cell></Row>'."\n";
            }
        }
        if($brok1!=null && $brok1!='')
        {
            if($brok2!=null && $brok2!='')
            {
                if($brok2!=$brok1)
                {
                    if($brok1>$brok2)
                    {
                        $temp=$brok1;
                        $brok1=$brok2;
                        $brok2=$temp;
                    }
                    $qry_brok_add="AND brokers.id>='$brok1' AND brokers.id<='$brok2'";
                }
                else
                {
                    $qry_brok_add="AND brokers.id='$brok1'";
                }
            }
            else
            {
                $qry_brok_add="AND brokers.id='$brok1'";
            }
            if(isset($_GET['save']))
            {
                $qbrok1=$sql->query("SELECT `abbr`, `name` FROM `brokers` WHERE id='$brok1';");
                $sbrok1=$sql->fetch_array($qbrok1);
                $content2.='<Row><Cell><Data ss:Type="String">'.$Lang['broker'].': '.$sbrok1['abbr'].' ('.$sbrok1['name'].') '.$Lang['to'];
                if($brok2!=$brok1)
                {
                    $qbrok2=$sql->query("SELECT `abbr`, `name` FROM `brokers` WHERE id='$brok2';");
                    $sbrok2=$sql->fetch_array($qbrok2);
                    $content2.=' '.$sbrok2['abbr'].' ('.$sbrok2['name'].') ';
                }
                else
                {
                    $content2.=' '.$sbrok1['abbr'].' ('.$sbrok1['name'].') ';
                }
                $content2.='</Data></Cell></Row>'."\n";
            }
        }
        if($taker!=null && $taker!='')
        {
            $qry_taker_add=" AND policies.taker_id='$taker'";
            if(isset($_GET['save']))
            {
                $qtaker=$sql->query("SELECT name FROM clients WHERE id='$taker';");
                $staker=$sql->fetch_array($qtaker);
                $content2.='<Row><Cell><Data ss:Type="String">'.$Lang['ins_taker'].': '.$staker['name'].'</Data></Cell></Row>'."\n";
            }
        }
        if($company!=null && $company!='')
        {
            $qry_comp_add=" AND policies.ins_comp_id='$company'";
            if(isset($_GET['save']))
            {
                $qcomp=$sql->query("SELECT name FROM insurance_companies WHERE id='$company';");
                $scomp=$sql->fetch_array($qcomp);
                $content2.='<Row><Cell><Data ss:Type="String">'.$Lang['ins_company'].': '.$scomp['name'].'</Data></Cell></Row>'."\n";
            }
        }
        switch($report)
        {
            case 'payments': //kaarteejie maksaajumi
                $page_foot=$sql->query("SELECT count(*) FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ((( policies LEFT JOIN clients ON policies.taker_id = clients.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) LEFT JOIN payments ON policies.id = payments.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE payments.col_date IS NULL AND canceled_policies.id IS NULL AND payments.pay_date >= '$qfrom' AND payments.pay_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add");
                
                $query="SELECT policies.id, policies.nr, policies.add_nr, policies.ins_comp_id, insurance_companies.abbr AS IC, ins_type.abbr AS IT, policies.taker_id, clients. NAME as taker, IF ( ISNULL(clients.real_address), clients.address, clients.real_address ) AS address, clients.telephone, payments.nr AS nrpk, payments.pay_date, payments.col_date, payments.sum AS PSUM, policies.add_disc_rate AS DSC, currency.abbr AS CRC, currency.id AS crc_id, IF(policies.add_disc_rate>0,(payments.sum - payments.sum * policies.add_disc_rate / 100 ),payments.sum) AS SUM_W_DSC, round(( payments.sum * policies.comm_rate / 100 ), 2 ) AS COMM, employees.abbr AS EMP, brokers.abbr AS BROKER, policies.client_attraction as ATTR, object_type.abbr AS OT, IF ( IsNull(objects.reg_nr), objects.loc, concat( objects.manufacturer, ', ', objects.model, ', ', objects.reg_nr )) AS OBJ FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ((( policies LEFT JOIN clients ON policies.taker_id = clients.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) LEFT JOIN payments ON policies.id = payments.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE payments.col_date IS NULL AND canceled_policies.id IS NULL AND payments.pay_date >= '$qfrom' AND payments.pay_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add $lim;";
                if(isset($_GET['save']))
                {
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">'.$Lang['regular_payments'].'</Data></Cell></Row>';
                    $content.=$content2;
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['payment_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['real_legal_address'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ins_object'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['pay_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['currency'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['payment_sum'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['sum_w_disc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comm'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['attraction'].'</Data></Cell></Row>';
                }
                else
                {
                    echo '<table border="1"><tr><td>'.$Lang['nr'].'</td><td>'.$Lang['add_nr'].'</td><td>'.$Lang['ic'].'</td><td>'.$Lang['payment_nr'].'</td><td>'.$Lang['taker'].'</td><td>'.$Lang['real_legal_address'].'</td><td>'.$Lang['ins_object'].'</td><td>'.$Lang['pay_date'].'</td><td>'.$Lang['currency'].'</td><td>'.$Lang['payment_sum'].'</td><td>'.$Lang['disc_perc'].'</td><td>'.$Lang['sum_w_disc'].'</td><td>'.$Lang['comm'].'</td><td>'.$Lang['broker'].'</td><td>'.$Lang['attraction'].'</td></tr>';
                }
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $pay_date=explode(' ',$row['pay_date']);
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['nrpk'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['taker'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['address'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['OBJ'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$pay_date[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['CRC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['PSUM'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['DSC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['SUM_W_DSC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['COMM'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['BROKER'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['ATTR'].'</Data></Cell></Row>'."\n";
                    }
                    else
                    {
                        echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$row['IC'].'</td><td>'.$row['nrpk'].'</td><td>'.$row['taker'].'</td><td>'.$row['address'].'</td><td>'.$row['OBJ'].'</td><td>'.$pay_date[0].'</td><td>'.$row['CRC'].'</td><td>'.$row['PSUM'].'</td><td>'.$row['DSC'].'</td><td>'.$row['SUM_W_DSC'].'</td><td>'.$row['COMM'].'</td><td>'.$row['BROKER'].'</td><td>'.$row['ATTR'].'</td></tr>';
                    }   
                    if($row['CRC']=='')
                        $row['CRC']='Other';
                    if(!isset($curr[$row['CRC']]))
                    {
                        $curr[$row['CRC']]=array();
                        $curr[$row['CRC']]['summ']=0;
                        $curr[$row['CRC']]['comm']=0;
                    }
                    $curr[$row['CRC']]['summ']+=$row['PSUM'];
                    $curr[$row['CRC']]['comm']+=$row['COMM'];
                }
                if(isset($_GET['save']))
                {
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">'.$Lang['total'].'</Data></Cell><Cell ss:styleID="s23"><Data ss:Type="String">'.$Lang['payment_count'].'</Data></Cell><Cell><Data ss:Type="Number">'.$sql->row_count.'</Data></Cell></Row>';
                    $content.='<Row></Row>';
                }
                else
                {
                    echo '</table><table border="1"><tr><td>'.$Lang['total'].'</td><td>'.$Lang['payment_count'].'</td><td>'.$sql->row_count.'</td><td></td></tr>';
                }
                if($curr!=null)
                {
                    foreach($curr as $key=>$values)
                    {
                        if(isset($_GET['save']))
                        {
                            $content.='<Row><Cell><Data ss:Type="String">'.$Lang['summ_to_be_payed'].' ('.$key.')</Data></Cell><Cell ss:styleID="s23"><Data ss:Type="Number">'.$values['summ'].'</Data></Cell><Cell><Data ss:Type="String">'.$Lang['comm'].' ('.$key.')</Data></Cell><Cell ss:styleID="s23"><Data ss:Type="Number">'.$values['comm'].'</Data></Cell></Row>'."\n";
                        }
                        else
                        {
                            echo '<tr><td>'.$Lang['summ_to_be_payed'].' ('.$key.')</td><td>'.$values['summ'].'</td><td>'.$Lang['comm'].' ('.$key.')</td><td>'.$values['comm'].'</td></tr>';
                        }
                    }
                    if(!isset($_GET['save']))
                    {
                        echo '</table>';
                    }
                }
            break;
            case 'ins_contracts': //vajadzeetu visam buut ok (emp un ins comp netesteets))
                if($qry_emp_add!=null && $qry_emp_add!='') $qry_emp_add.=' AND brokers.id IS NULL';
                $page_foot=$sql->query("SELECT count(*) FROM object_type RIGHT JOIN ( objects RIGHT JOIN (( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN (( policies LEFT JOIN clients AS oclient ON policies.owner_id = oclient.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN clients AS nclient ON policies.taker_id = nclient.id ) LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE canceled_policies.id IS NULL AND policies.issue_date >= '$qfrom' AND policies.issue_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add;");
                $query="SELECT policies.id, policies.nr, policies.add_nr, insurance_companies.id AS as_id, insurance_companies.abbr AS IC, ins_type.id AS veids_id, ins_type.abbr AS IT, oclient.NAME AS owner, policies.taker_id, policies.issue_date, policies.from_date, policies.to_date, currency.id AS val_id, currency.abbr AS CRC, policies.ins_prem, policies.add_disc_rate, policies.comm_rate, policies.broker_rate, policies.broker_id, brokers.abbr AS starp, policies.employee_id, employees.abbr AS darbin, nclient.NAME AS taker, IF ( IsNull(objects.reg_nr), objects.loc, concat( objects.manufacturer, ', ', objects.model, ', ', objects.reg_nr )) AS OBJ, round( IF ( policies.add_disc_rate > 0, policies.ins_prem * policies.add_disc_rate / 100, 0 ), 2 ) AS ADD_DSC, round( IF ( policies.comm_rate > 0, policies.ins_prem * policies.comm_rate / 100, 0 ), 2 ) AS COMM, round( IF ( policies.broker_rate > 0, policies.ins_prem * policies.broker_rate / 100, 0 ), 2 ) AS BROK FROM object_type RIGHT JOIN ( objects RIGHT JOIN (( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN (( policies LEFT JOIN clients AS oclient ON policies.owner_id = oclient.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN clients AS nclient ON policies.taker_id = nclient.id ) LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE canceled_policies.id IS NULL AND policies.issue_date >= '$qfrom' AND policies.issue_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add $lim;";
                if(isset($_GET['save']))
                {
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">'.$Lang['concluded_insurance_contracts'].'</Data></Cell></Row>';
                    $content.=$content2;
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['insurance_type'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker_owner'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ins_object'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['issue_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['from_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['to_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['currency'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_premium'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comm_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker'].'</Data></Cell></Row>';
                }
                else
                {
                    echo '<table border="1"><tr><td>'.$Lang['nr'].'</td><td>'.$Lang['add_nr'].'</td><td>'.$Lang['ic'].'</td><td>'.$Lang['insurance_type'].'</td><td>'.$Lang['taker_owner'].'</td><td>'.$Lang['ins_object'].'</td><td>'.$Lang['issue_date'].'</td><td>'.$Lang['from_date'].'</td><td>'.$Lang['to_date'].'</td><td>'.$Lang['currency'].'</td><td>'.$Lang['signed_premium'].'</td><td>'.$Lang['disc_perc'].'</td><td>'.$Lang['comm_perc'].'</td><td>'.$Lang['broker_perc'].'</td><td>'.$Lang['broker'].'</td></tr>';
                }
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $issdate=explode(' ',$row['issue_date']);
                    $frdate=explode(' ',$row['from_date']);
                    $todate=explode(' ',$row['to_date']);
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IT'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['owner'].' / '.$row['taker'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['OBJ'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$issdate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$frdate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$todate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['CRC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['ins_prem'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['ADD_DSC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['COMM'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['BROK'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['starp'].'</Data></Cell></Row>'."\n";
                    }
                    else
                    {
                        echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$row['IC'].'</td><td>'.$row['IT'].'</td><td>'.$row['owner'].' / '.$row['taker'].'</td><td>'.$row['OBJ'].'</td><td>'.$issdate[0].'</td><td>'.$frdate[0].'</td><td>'.$todate[0].'</td><td>'.$row['CRC'].'</td><td>'.$row['ins_prem'].'</td><td>'.$row['ADD_DSC'].'</td><td>'.$row['COMM'].'</td><td>'.$row['BROK'].'</td><td>'.$row['starp'].'</td></tr>';
                    }
                    if($row['CRC']=='')
                        $row['CRC']='Other';
                    if(!isset($curr[$row['CRC']]))
                    {
                        $curr[$row['CRC']]=array();
                        $curr[$row['CRC']]['cnt']=0;
                        $curr[$row['CRC']]['prem']=0;
                        $curr[$row['CRC']]['comm']=0;
                        $curr[$row['CRC']]['dsc']=0;
                        $curr[$row['CRC']]['brok']=0;
                    }
                    $curr[$row['CRC']]['cnt']++;
                    $curr[$row['CRC']]['prem']+=$row['ins_prem'];
                    $curr[$row['CRC']]['comm']+=$row['COMM'];
                    $curr[$row['CRC']]['dsc']+=$row['ADD_DSC'];
                    $curr[$row['CRC']]['brok']+=$row['BROK'];
                }
                if(isset($_GET['save']))
                {
                    $content .='<Row></Row>'."\n";
                }
                else
                {
                    echo '</table>';
                }
                if(count($curr)>0)
                {
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['total'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="String">'.$key.'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                    
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['policy_count'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.$curr[$key]['cnt'].'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_premium'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['prem'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_commision'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['comm'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_discount'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['dsc'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_broker_perc'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['brok'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                    }
                    else
                    {
                        echo '<table border="1"><tr><td>'.$Lang['total'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.$key.'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['policy_count'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.$curr[$key]['cnt'].'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_premium'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['prem'],2).'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_commision'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['comm'],2).'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_discount'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['dsc'],2).'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_broker_perc'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['brok'],2).'</td>';
                        }
                        echo '</tr></table>';
                    }
                }
            break;
            case 'rpayments':
                $page_foot=$sql->query("SELECT count(*) FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ((( policies LEFT JOIN clients AS tclient ON policies.taker_id = tclient.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) LEFT JOIN payments ON policies.id = payments.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE payments.col_date >= '$qfrom' AND payments.col_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add;");
                $query="SELECT policies.id, policies.nr, policies.add_nr, policies.ins_comp_id, insurance_companies.abbr AS IC, ins_type.abbr AS IT, tclient. NAME as taker, IF ( IsNull(tclient.real_address), tclient.address, tclient.real_address ) AS adrese, tclient.telephone, payments.nr AS NRPK, payments.pay_date, payments.col_date, payments.sum AS PSUM, currency.abbr AS CRC, currency.id AS CRC_ID, policies.add_disc_rate, round( IF ( policies.add_disc_rate > 0, payments.sum * policies.add_disc_rate / 100, 0 ), 2 ) AS ADD_DSC, round( IF ( policies.comm_rate > 0, payments.sum * policies.comm_rate / 100, 0 ), 2 ) AS COMM, round( IF ( policies.broker_rate > 0, payments.sum * policies.broker_rate / 100, 0 ), 2 ) AS BROK, employees.abbr AS EMP, brokers.id, brokers.abbr AS BROKER, canceled_policies.id AS CANCELED, canceled_policies.rId, policies.client_attraction, object_type.abbr AS OT, IF ( IsNull(objects.reg_nr), objects.loc, concat( objects.manufacturer, ', ', objects.model, ', ', objects.reg_nr )) AS OBJ FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ((( policies LEFT JOIN clients AS tclient ON policies.taker_id = tclient.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) LEFT JOIN payments ON policies.id = payments.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE payments.col_date >= '$qfrom' AND payments.col_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add $lim;";
                if(isset($_GET['save']))
                {
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">'.$Lang['charged_payments'];
                    $content.='</Data></Cell></Row>';
                    $content.=$content2;
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ins_object'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['charged_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['payment_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['currency'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['payment_sum'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_crc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comm_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comm_crc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker_crc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['canceled'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['attraction'].'</Data></Cell></Row>';
                }
                else
                {
                    echo '<table border="1"><tr><td>'.$Lang['nr'].'</td><td>'.$Lang['add_nr'].'</td><td>'.$Lang['ic'].'</td><td>'.$Lang['taker'].'</td><td>'.$Lang['ins_object'].'</td><td>'.$Lang['charged_date'].'</td><td>'.$Lang['payment_nr'].'</td><td>'.$Lang['currency'].'</td><td>'.$Lang['payment_sum'].'</td><td>'.$Lang['disc_perc'].'</td><td>'.$Lang['disc_crc'].'</td><td>'.$Lang['comm_perc'].'</td><td>'.$Lang['comm_crc'].'</td><td>'.$Lang['broker_perc'].'</td><td>'.$Lang['broker_crc'].'</td><td>'.$Lang['broker'].'</td><td>'.$Lang['canceled'].'</td><td>'.$Lang['attraction'].'</td></tr>';
                }
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $coldate=explode(' ',$row['col_date']);
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['taker'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['OBJ'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$coldate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['NRPK'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['CRC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['PSUM'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['add_disc_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['ADD_DSC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['comm_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['COMM'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['broker_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['BROK'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['BROKER'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['rId'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['client_attraction'].'</Data></Cell></Row>'."\n";
                    }
                    else
                    {
                        echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$row['IC'].'</td><td>'.$row['taker'].'</td><td>'.$row['OBJ'].'</td><td>'.$coldate[0].'</td><td>'.$row['NRPK'].'</td><td>'.$row['CRC'].'</td><td>'.$row['PSUM'].'</td><td>'.$row['add_disc_rate'].'</td><td>'.$row['ADD_DSC'].'</td><td>'.$row['comm_rate'].'</td><td>'.$row['COMM'].'</td><td>'.$row['broker_rate'].'</td><td>'.$row['BROK'].'</td><td>'.$row['BROKER'].'</td><td>'.$row['rId'].'</td><td>'.$row['client_attraction'].'</td></tr>';
                    }
                    if($row['CRC']=='')
                        $row['CRC']='Other';
                    if(!isset($curr[$row['CRC']]))
                    {
                        $curr[$row['CRC']]=array();
                        $curr[$row['CRC']]['cnt']=0;
                        $curr[$row['CRC']]['prem']=0;
                        $curr[$row['CRC']]['comm']=0;
                        $curr[$row['CRC']]['dsc']=0;
                        $curr[$row['CRC']]['brok']=0;
                    }
                    $curr[$row['CRC']]['cnt']++;
                    $curr[$row['CRC']]['prem']+=$row['PSUM'];
                    $curr[$row['CRC']]['comm']+=$row['COMM'];
                    $curr[$row['CRC']]['dsc']+=$row['ADD_DSC'];
                    $curr[$row['CRC']]['brok']+=$row['BROK'];
                }
                if(isset($_GET['save']))
                {
                    $content .='<Row></Row>'."\n";
                }
                else
                {
                    echo '</table>';
                }
                if(count($curr)>0)
                {
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['total'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="String">'.$key.'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                    
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['policy_count'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.$curr[$key]['cnt'].'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_premium'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['prem'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_commision'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['comm'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_discount'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['dsc'],2).'</Data></Cell>';
                        }
                        $content .='</Row>'."\n";
                        $content .='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_broker_perc'].'</Data></Cell>';
                        foreach($curr as $key=>$values)
                        {
                            $content .= '<Cell ss:StyleID="s22"><Data ss:Type="Number">'.round($curr[$key]['brok'],2).'</Data></Cell>';
                        }
                    
                        $content .='</Row>'."\n";
                    }
                    else
                    {
                        echo '<table border="1"><tr><td>'.$Lang['total'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.$key.'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['policy_count'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.$curr[$key]['cnt'].'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_premium'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['prem'],2).'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_commision'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['comm'],2).'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_discount'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['dsc'],2).'</td>';
                        }
                        echo '</tr><tr><td>'.$Lang['signed_broker_perc'].'</td>';
                        foreach($curr as $key=>$values)
                        {
                            echo '<td>'.round($curr[$key]['brok'],2).'</td>';
                        }
                        echo'</tr></table>';
                    }
                }
            break;
            case 'pol_restore':
                $page_foot=$sql->query("SELECT count(*) FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN (( policies LEFT JOIN clients ON policies.taker_id = clients.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE canceled_policies.id IS NULL AND policies.to_date >= '$qfrom' AND policies.to_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add;");
                $query="SELECT policies.id, policies.nr, policies.add_nr, policies.to_date, insurance_companies.abbr AS IC, clients. NAME as taker, IF ( IsNull(clients.real_address), clients.address, clients.real_address ) AS adrese, clients.telephone, policies.add_disc_rate, policies.ins_prem, currency.abbr AS CRC, currency.id AS val_id, employees.abbr AS EMP, brokers.abbr AS BROKER, object_type.abbr AS OT, IF ( IsNull(objects.reg_nr), objects.loc, concat( objects.manufacturer, ', ', objects.model, ', ', objects.reg_nr )) AS OBJ FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN (( policies LEFT JOIN clients ON policies.taker_id = clients.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE canceled_policies.id IS NULL AND policies.to_date >= '$qfrom' AND policies.to_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add $lim;";
                if(isset($_GET['save']))
                {
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">Polises kuru darbības laiks beidzās</Data></Cell></Row>';
                    $content.=$content2;
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['to_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['real_legal_address'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ins_object'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['currency'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['signed_premium'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker'].'</Data></Cell></Row>';
                }
                else
                {
                    echo '<table border="1"><tr><td>'.$Lang['nr'].'</td><td>'.$Lang['add_nr'].'</td><td>'.$Lang['to_date'].'</td><td>'.$Lang['ic'].'</td><td>'.$Lang['taker'].'</td><td>'.$Lang['real_legal_address'].'</td><td>'.$Lang['ins_object'].'</td><td>'.$Lang['currency'].'</td><td>'.$Lang['signed_premium'].'</td><td>'.$Lang['disc_perc'].'</td><td>'.$Lang['broker'].'</td></tr>';
                }
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $todate=explode(' ',$row['to_date']);
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$todate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['taker'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['adrese'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['OBJ'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['CRC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['ins_prem'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['add_disc_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['BROKER'].'</Data></Cell></Row>'."\n";
                    }
                    else
                    {
                        echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$todate[0].'</td><td>'.$row['IC'].'</td><td>'.$row['taker'].'</td><td>'.$row['adrese'].'</td><td>'.$row['OBJ'].'</td><td>'.$row['CRC'].'</td><td>'.$row['ins_prem'].'</td><td>'.$row['add_disc_rate'].'</td><td>'.$row['BROKER'].'</td><td>'."\n";
                    }
                }
                if(isset($_GET['save']))
                {
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s23"><Data ss:Type="String">'.$Lang['policy_count'].'</Data></Cell><Cell ss:StyleID="s23"><Data ss:Type="Number">'.$sql->row_count.'</Data></Cell></Row>';
                }
                else
                {
                    echo '</table><table border="1"><tr><td>'.$Lang['policy_count'].'</td><td>'.$sql->row_count.'</td></tr></table>';
                }
            break;
            //te
            case 'pol_payable':
                $page_foot=$sql->query("SELECT count(*) FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ((( policies LEFT JOIN clients ON policies.taker_id = clients.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) LEFT JOIN payments ON policies.id = payments.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE payments.col_date IS NULL AND canceled_policies.id IS NULL AND payments.pay_date < NOW() AND policies.issue_date >= '$qfrom' AND policies.issue_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add;");
                $query="SELECT policies.id, policies.nr, policies.issue_date, policies.add_nr, policies.ins_comp_id, insurance_companies.abbr AS IC, ins_type.abbr AS IT, policies.taker_id, clients. NAME as taker, IF ( IsNull(clients.real_address), clients.address, clients.real_address ) AS adrese, clients.telephone, payments.nr AS NRPK, payments.pay_date, payments.col_date, payments.sum, policies.add_disc_rate, currency.abbr AS CRC, currency.id AS CRC_ID, payments.sum - ( payments.sum * policies.add_disc_rate / 100 ) AS SUM_W_DSC, payments.sum * policies.comm_rate / 100 AS COMM, employees.abbr AS EMP, brokers.abbr AS BROKER, object_type.abbr AS OT, IF ( IsNull(objects.reg_nr), objects.loc, concat( objects.manufacturer, ', ', objects.model, ', ', objects.reg_nr )) AS OBJ FROM object_type RIGHT JOIN ( objects RIGHT JOIN ( currency RIGHT JOIN ( brokers RIGHT JOIN ( employees RIGHT JOIN ( insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ((( policies LEFT JOIN clients ON policies.taker_id = clients.id ) LEFT JOIN canceled_policies ON policies.id = canceled_policies.id ) LEFT JOIN payments ON policies.id = payments.id ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id ) ON employees.id = policies.employee_id ) ON brokers.id = policies.broker_id ) ON currency.id = policies.currency_id LEFT JOIN pol_obj ON policies.id = pol_obj.pId ) ON objects.id = pol_obj.oId ) ON object_type.id = objects.tId WHERE payments.col_date IS NULL AND canceled_policies.id IS NULL AND payments.pay_date < NOW() AND policies.issue_date >= '$qfrom' AND policies.issue_date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add $lim;";
                if(isset($_GET['save']))
                {
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">Neapmaksātās polises, izdotās</Data></Cell></Row>';
                    $content.=$content2;
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['issue_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['payment_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['real_legal_address'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ins)object'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['pay_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['currency'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['payment_sum'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['disc_perc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['sum_w_disc'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comm'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['broker'].'</Data></Cell></Row>';
                }
                else
                {
                    echo '<table border="1"><tr><td>'.$Lang['nr'].'</td><td>'.$Lang['add_nr'].'</td><td>'.$Lang['issue_date'].'</td><td>'.$Lang['ic'].'</td><td>'.$Lang['payment_nr'].'</td><td>'.$Lang['taker'].'</td><td>'.$Lang['real_legal_address'].'</td><td>'.$Lang['ins)object'].'</td><td>'.$Lang['pay_date'].'</td><td>'.$Lang['currency'].'</td><td>'.$Lang['payment_sum'].'</td><td>'.$Lang['disc_perc'].'</td><td>'.$Lang['sum_w_disc'].'</td><td>'.$Lang['comm'].'</td><td>'.$Lang['broker'].'</td></tr>';
                }
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $issdate=explode(' ',$row['issue_date']);
                    $paydate=explode(' ',$row['pay_date']);
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$issdate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['NRPK'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['taker'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['adrese'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['OBJ'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$paydate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['CRC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['sum'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['add_disc_rate'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['SUM_W_DSC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$row['COMM'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['BROKER'].'</Data></Cell></Row>'."\n";
                    }
                    else
                    {
                        echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$issdate[0].'</td><td>'.$row['IC'].'</td><td>'.$row['NRPK'].'</td><td>'.$row['taker'].'</td><td>'.$row['adrese'].'</td><td>'.$row['OBJ'].'</td><td>'.$paydate[0].'</td><td>'.$row['CRC'].'</td><td>'.$row['sum'].'</td><td>'.$row['add_disc_rate'].'</td><td>'.$row['SUM_W_DSC'].'</td><td>'.$row['COMM'].'</td><td>'.$row['BROKER'].'</td></tr>';
                    }
                    if($row['CRC']=='')
                        $row['CRC']='Other';
                    if(!isset($curr[$row['CRC']]))
                    {
                        $curr[$row['CRC']]=array();
                        $curr[$row['CRC']]['sum']=0;
                        $curr[$row['CRC']]['comm']=0;

                    }
                    $curr[$row['CRC']]['comm']+=$row['COMM'];
                    $curr[$row['CRC']]['sum']+=$row['sum'];
                }
                if(isset($_GET['save']))
                {
                    $content .='<Row></Row>'."\n";
                }
                else
                {
                    echo '</table>';
                }
                if(count($curr)>0)
                {
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s23"><Data ss:Type="String">'.$Lang['total'].'</Data></Cell><Cell><Data ss:Type="String">'.$Lang['payment_count'].'</Data></Cell><Cell ss:StyleID="s23"><Data ss:Type="Number">'.$sql->row_count.'</Data></Cell></Row>';
                    }
                    else
                    {
                        echo '<table border="1"><tr><td>'.$Lang['total'].'</td><td>'.$Lang['payment_count'].'</td><td>'.$sql->row_count.'</td></tr>';
                    }
                    foreach($curr as $key=>$values)
                    {
                        if(isset($_GET['save']))
                        {
                            $content .= '<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$Lang['sum_to_be_payed'].' ('.$key.')</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$values['sum'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$Lang['comm'].' ('.$key.')</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="Number">'.$values['comm'].'</Data></Cell></Row>';
                        }
                        else
                        {
                            echo '<tr><td>'.$Lang['sum_to_be_payed'].' ('.$key.')</td><td>'.$values['sum'].'</td><td>'.$Lang['comm'].' ('.$key.')</td><td>'.$values['comm'].'</td><td></tr>';
                        }
                    }
                    if(!isset($_GET['save']))
                    {
                        echo '</table>';
                    }
                }
            break;
            case 'pol_canceled':
                $page_foot=$sql->query("SELECT count(*) FROM insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ( canceled_reasons RIGHT JOIN (( policies LEFT JOIN clients ON policies.taker_id = clients.id ) RIGHT JOIN canceled_policies ON policies.id = canceled_policies.id ) ON canceled_reasons.id = canceled_policies.rId ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id WHERE canceled_policies.date >= '$qfrom' AND canceled_policies.date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add;");
                $query="SELECT canceled_policies.id, policies.nr, policies.add_nr, policies.ins_comp_id, insurance_companies.abbr AS IC, policies.ins_type_id, ins_type.abbr AS IT, canceled_policies.date, canceled_reasons. NAME AS REASON, canceled_policies.`comment`, clients. NAME AS taker FROM insurance_companies RIGHT JOIN ( ins_type RIGHT JOIN ( canceled_reasons RIGHT JOIN (( policies LEFT JOIN clients ON policies.taker_id = clients.id ) RIGHT JOIN canceled_policies ON policies.id = canceled_policies.id ) ON canceled_reasons.id = canceled_policies.rId ) ON ins_type.id = policies.ins_type_id ) ON insurance_companies.id = policies.ins_comp_id WHERE canceled_policies.date >= '$qfrom' AND canceled_policies.date <= '$qto' $qry_emp_add $qry_brok_add $qry_taker_add $qry_comp_add $lim;";
                if(isset($_GET['save']))
                {
                    $content.='<Row><Cell ss:styleID="s23"><Data ss:Type="String">'.$Lang['canceled_policies'].'</Data></Cell></Row>';
                    $content.=$content2;
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['add_nr'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['cancel_date'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['ic'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['type'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['taker'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['cancel_reason'].'</Data></Cell><Cell ss:StyleID="s21"><Data ss:Type="String">'.$Lang['comment'].'</Data></Cell></Row>';
                }
                else
                {
                    echo '<table border="1"><tr><td>'.$Lang['nr'].'</td><td>'.$Lang['add_nr'].'</td><td>'.$Lang['cancel_date'].'</td><td>'.$Lang['ic'].'</td><td>'.$Lang['type'].'</td><td>'.$Lang['taker'].'</td><td>'.$Lang['cancel_reason'].'</td><td>'.$Lang['comment'].'</td></tr>';
                }
                $sql->query($query);
                while($row=$sql->fetch_array())
                {
                    $cancdate=explode(' ',$row['date']);
                    if(isset($_GET['save']))
                    {
                        $content .='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['add_nr'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$cancdate[0].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IC'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['IT'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['taker'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['REASON'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$row['comment'].'</Data></Cell></Row>'."\n";
                    }
                    else
                    {
                        echo '<tr><td>'.$row['nr'].'</td><td>'.$row['add_nr'].'</td><td>'.$cancdate[0].'</td><td>'.$row['IC'].'</td><td>'.$row['IT'].'</td><td>'.$row['taker'].'</td><td>'.$row['REASON'].'</td><td>'.$row['comment'].'</td></tr>';
                    }
                }
                if(isset($_GET['save']))
                {
                    $content.='<Row></Row>';
                    $content.='<Row><Cell ss:StyleID="s22"><Data ss:Type="String">'.$Lang['policy_count'].'</Data></Cell><Cell ss:StyleID="s22"><Data ss:Type="String">'.$sql->row_count.'</Data></Cell></Row>';
                }
                else
                {
                    echo '</table><table border="1"><tr><td>'.$Lang['policy_count'].'</td><td>'.$sql->row_count.'</td></tr></table>';
                }
            break;
        }
        if(isset($_GET['save']))
        {
            $content .=writeXLSFoot();
            $fn=md5($qfrom.$qto.getVar('report_type').time());
            writeFile('files/'.$fn.'.xml',$content);
            echo '<a href="download.php?id='.$fn.'.xml&del=1">'.$Lang['download_file'].'</a>';
        }
        else
        {
            $page_foot = $sql->result($page_foot);
            echo pagechoose($start + 1, $page_foot, $npage);
            echo '<a href="?p='.$page.'&date_from='.$from.'&date_to='.$to.'&ins_comps='.$company.'&ins_takers='.$taker.'&report_type='.$report.'&employee1='.$emp1.'&employee2='.$emp2.'&broker1='.$brok1.'&broker2='.$brok2.'&save"><img src="img/save.png" alt="'.$Lang['save'].'" title="'.$Lang['save'].'" width="32" heigth="32" /></a><br />';
        }
        
    }
    catch(Exception $e)
    {
        msg($Lang['error'],$e->get_message(),'error');
    }
}
?>